package com.yys.excel.yysexcel.utils.easyPoi.excelExample;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.lang.Dict;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import com.yys.excel.yysexcel.utils.*;
import com.yys.excel.yysexcel.utils.dbUtil.DbUtil;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 开挖监管excel导出
 */
public class KwjgExcelPoi {
    public static void main(String[] args) {
        excute(FileUtils.getFilePathByName("名字22"));
    }

    public static void excute(String filePath) {
        //获取sql查询的数据
        List<Entity> data = getData();
        //模板文件路径
        String templatePath = "templates/excel/template/开挖监管统计表.xlsx";
        String ossPath = "test/template/开挖监管统计表.xlsx";
        //模板需要的数据
        Dict dict = Dict.create();
        dict.set("date", DateUtil.now());
        dict.set("name", "张三");
        dict.set("datas", data);
        //数据写入到模板中
        Workbook workbook = ExcelExportUtil.exportExcel(TemplateUtils.getTemplateByOssPath(ossPath), dict);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filePath);
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static List<Entity> getData() {
        String sqlDatas = "select top 1 a.*, sd.valuename statusname,case when c.VALUE is not null then c.VALUE else '无证偷挖' end licensename,u.DISPLAYNAME,cu.unitName constructionUnitName,ISNULL(ap.appendNum , '0') appendNum from Excavation_branch a left join ConstructionUnit cu on a.constructionUnit = cu.unitCode left join (SELECT damnum, cast(count(*) as varchar) appendNum FROM Excavation_branch_log WHERE isdelete = '0' group by damnum) ap on a.damnum = ap.damnum  LEFT JOIN sysDict sd ON sd.value = a.statusCode AND sd.type= 8 left join (select * from DFCCODE where typeid = '3005') c on c.NAME = a.haveLicense left join USER_INFO u on a.addPerson = u.USERNAME where a.isdelete = '0'  order by a.addTime desc";
        List<Entity> querys = DbUtil.querys(sqlDatas);

        String tableName = "Excavation_branch";
        int numIndex = 1;
        for (Entity map : querys) {
            String num = "";
            String sql = "";
            String gjNum = "";
            //查询跟进次数 和 最后5次跟进总进度
            if("Excavation".equalsIgnoreCase(tableName)){
                String damnum = map.getStr("damnum");
                gjNum = "SELECT COUNT (0) num FROM "+tableName+"_log WHERE isdelete='0' AND damnum='"+damnum+"'";
                sql = "SELECT top 5 (CAST(addTime as varchar(20))+' '+CAST(treatmentName as varchar(100))) treatmentName FROM "+tableName+"_log WHERE isdelete = '0' AND damnum = '"+damnum+"' ORDER BY addTime desc";
            }else {
                String pid = map.getStr("pid");
                //分之流程就查询该条分支的数据
                gjNum = "SELECT COUNT (0) num FROM "+tableName+"_log WHERE isdelete='0' AND pid='"+pid+"'";
                sql = "SELECT top 5 (CAST(addTime as varchar(20))+' '+CAST(treatmentName as varchar(100))) treatmentName FROM "+tableName+"_log WHERE isdelete = '0' AND pid = '"+pid+"' ORDER BY addTime desc";
            }
            num = DbUtil.query(gjNum).getStr("num");
            map.put("appendNum",num);
            List<Entity> appendLog = DbUtil.querys(sql);
            if (appendLog.size() == 0) {
                map.put("ztgjjd", " ");
            } else {
                String ztgjjdStr = "";
                for (int i = appendLog.size(); i > 0; i--) {
                    ztgjjdStr += appendLog.get(i-1).getStr("treatmentName") + "\n";
                }
                map.put("ztgjjd", ztgjjdStr);
            }
            //将现场描述的~t  转化为换行
            String excavateContentName = map.get("excavateContentName").toString();
            if (StrUtil.isNotEmpty(excavateContentName)) {
                String s = excavateContentName.replaceAll("~t", "\n");
                map.put("excavateContentName", s);
            }

            //合并联系人+电话
            String personTel = map.getStr("person") + "\n" + map.getStr("tel");
            map.put("person", personTel);

            //查询该编号的所有操作记录id
            String damnum = map.getStr("damnum");
            String sqlIds = "select id  from "+tableName+"_log where damnum = '"+damnum+"' order by addTime";

            byte[] firstImg = getImgByte(sqlIds, true);

            byte[] lastImg = getImgByte(sqlIds, false);


            ImageEntity imageFirst = new ImageEntity(firstImg,27,165);

            ImageEntity imageLast = new ImageEntity(lastImg,27,165);

            map.put("imageFirst", imageFirst);
            map.put("imageLast", imageLast);

            map.put("num", numIndex);
            numIndex++;
        }
        return querys;
    }

    public static byte[] getImgByte(String sqlIds,boolean isFirst) {
        //查询所有的记录图片
        List<Entity> querysIds = DbUtil.querys(sqlIds);
        List<String> ids = new ArrayList<>();
        for (Entity querysId : querysIds) {
            String id = querysId.getStr("id");
            ids.add(id);
        }
        if(isFirst){
            //查询第一张图片
            byte[] firstImg = null;
            for (int i = 0; i < ids.size(); i++) {
                String id = ids.get(i);
                firstImg = AttachUtils.fileByteByPid(id, true);
                if(firstImg != null){
                    return firstImg;
                }
            }
        }else {
            //最后一张图片
            byte[] lastImg = null;
            for (int i = ids.size(); i > 0; i--) {
                String dataId = ids.get(i - 1);
                lastImg = AttachUtils.fileByteByPid(dataId, false);
                if(lastImg != null){
                    return lastImg;
                }
            }
        }
        return null;
    }
}
